package server;

import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;

import utils.EscapeTool;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import db.DataSourceFactory;
import db.DbUtil;
import db.MDTMySQLRowMapper;
import db.TransRunner;

public class SaveDAO {
	public void insertDTO(JSONObject article) throws SQLException{
		String content = new EscapeTool().html(article.get("content").toString());
		String fixedKeyword = article.get("fixedKeyword").toString();
//			new StringBuffer();
//		for(String s:article.getFixedKeyword()){
//			fixedKeyword.append(s+",");
//		}
//		fixedKeyword.deleteCharAt(fixedKeyword.lastIndexOf(","));
		String sql = "insert into test.article(FixedKeyword,UserKeyword,Url,Title,Content,CreateTime,UpdateTime) values (";
		sql+= DbUtil.escSql(fixedKeyword.toString())+","+DbUtil.escSql(article.get("userKeyword"))+","+DbUtil.escSql(article.get("url"))+",";
		sql+= DbUtil.escSql(article.get("title"))+","+DbUtil.escSql(content)+",now(),"+"now()"+")";
		TransRunner runner = new TransRunner(DataSourceFactory.getDataSource("test","ChinacaT"), new MDTMySQLRowMapper());
    	runner.update(sql); 
	}
//	
//	public List<JSONObject> getDTO(String keyword) throws SQLException{
//		List<JSONObject> ret = new ArrayList<JSONObject>();
//		String sql = "select * from test.article where FixedKeyword like "+DbUtil.escSql(keyword)+"%";
//		TransRunner runner = new TransRunner(DataSourceFactory.getDataSource(), new MDTMySQLRowMapper());
//		List<Map> result = runner.query(sql);
//		return ret;
//	}
	
	public JSONArray getDTO(String keyword) throws SQLException{
		JSONArray ret = new JSONArray();
		String sql = "select * from test.article" ;
		if(!keyword.equals(""))sql+=" where FixedKeyword like '%"+keyword+"%'";
		TransRunner runner = new TransRunner(DataSourceFactory.getDataSource("test","ChinacaT"), new MDTMySQLRowMapper());
		List<Map> result = runner.query(sql);
		if(result.size()>0){
		for(Map m:result){
			JSONObject jo = JSONObject.fromObject(m);
			ret.add(formatJO(jo,m));
		}
		return ret;
		}
		return null;
	}
	
	private JSONObject formatJO(JSONObject jo,Map map){
		DateFormat dateFormat  = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
		if(jo.containsKey("UpdateTime")){
			jo.remove("UpdateTime");
			Date date = (Date)map.get("UpdateTime");
			String d = dateFormat.format(date);
			jo.element("UpdateTime", d);
		}
		if(jo.containsKey("CreateTime")){
			jo.remove("CreateTime");
			Date date = (Date)map.get("CreateTime");
			String d = dateFormat.format(date);
			jo.element("CreateTime", d);
		}
		if(jo.containsKey("Content")){
			jo.remove("Content");
			jo.element("Content", StringEscapeUtils.unescapeHtml(map.get("Content").toString()));
		}
		return jo;
	}
	public List<Map> getTitles(String keyword) throws SQLException{
		String sql = "select * from test.article where Title='null' and Url='Null'";
		TransRunner runner = new TransRunner(DataSourceFactory.getDataSource("test","ChinacaT"), new MDTMySQLRowMapper());
    	List<Map> ret = runner.query(sql);
    	return ret;
	}
}
